const sql = require('mssql');
const baseFn = require('../../public/base.js');
/**
 * 
 *门店采购审核
 */

//  销退开始时间
let startTime = '2019-05-17 00:00:00';
// let startTime = '';
//  获得订单信息

const getVerifyStorePurchaseOrder = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Z.ID) AS px,Z.RQ,Z.ID,Z.BMBM,Z.DWMC,Z.DWBM,Z.BMMC,Z.JSFSBM,Z.JSFSMC,Z.ZSL,Z.ZJE,Z.JBRMC,B.DH FROM DHD_ZB Z 
    LEFT JOIN BM_WLDW B ON Z.DWBM = B.BM  WHERE Z.RQ <= '${body.searchVal.timeRange[1]} 23:59:59' AND Z.RQ >= '${body.searchVal.timeRange[0]} 00:00:00' AND Z.RQ >= '${startTime}' 
    AND Z.BMBM='${body.searchVal.BMBM}' AND Z.SH = 'F' `;
    let totalSql = `SELECT COUNT(Z.ID) AS TOTAL FROM DHD_ZB Z 
    LEFT JOIN BM_WLDW B ON Z.DWBM = B.BM  WHERE Z.RQ <= '${body.searchVal.timeRange[1]} 23:59:59' AND Z.RQ >= '${body.searchVal.timeRange[0]} 00:00:00' AND Z.RQ >= '${startTime}' 
    AND Z.BMBM='${body.searchVal.BMBM}' AND Z.SH = 'F' `
    if (body.searchVal.GYS) {
        startSql += ` AND (B.MC LIKE '%${body.searchVal.GYS}%' OR B.JP LIKE '%${body.searchVal.GYS}%')`;
        totalSql += ` AND (B.MC LIKE '%${body.searchVal.GYS}%' OR B.JP LIKE '%${body.searchVal.GYS}%')`;
    }
    startSql += `) AS t1 WHERE t1.px between ${(body.pageNum - 1) * 5 + 1} and ${body.pageNum * 5}`;
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = {
        data: result.recordset,
        total: total.recordset[0].TOTAL
    };
}


//  获得订单对应的商品信息
const getVerifyStorePurchaseShop = async (ctx, next) => {
    const body = ctx.request.body;
    let nowTime = baseFn.getNowTimeId().fullDate;
    let agoTime = baseFn.getDayAgo(30);
    let startSql = `SELECT Y.JFGZ,Y.ZDJ,M.YPBM AS BM,M.YPMC AS MC,M.YPTM AS TM,M.JXMC,M.YPGG AS GG,M.JLDW,M.SCCJ,ISNULL(M.CDMC,'') AS CDMC,M.PZWH,M.YXQX,
    M.PFJ,M.HYJ,M.SL AS DHSL,M.DJ,M.JE,M.MJSL,M.JS,M.SS,M.LSJ,KC.KYSL,KC.KCSL,ZKC.KCSL AS ZBKC,ZJJ.DJ AS ZJDJ,XL.SL AS XSSL  
    FROM DHD_MX M LEFT JOIN BM_YP Y ON M.YPBM = Y.BM  LEFT JOIN (SELECT SUM(KCSL) AS KCSL,SUM(KYSL) AS KYSL,YPBM FROM KC_KCDTB WHERE BMBM = '${body.BMBM}' AND KCSL>0 GROUP BY YPBM) KC ON M.YPBM = KC.YPBM 
    LEFT JOIN (SELECT SUM(KCSL) AS KCSL,YPBM FROM KC_KCDTB WHERE BMBM = '07' AND KCSL>0 GROUP BY YPBM) ZKC ON M.YPBM = ZKC.YPBM 
    LEFT JOIN (SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY mx.YPBM ORDER BY zb.RQ DESC) AS num,mx.DJ,mx.YPBM FROM dbo.XSD_ZB AS zb 
    LEFT JOIN dbo.XSD_MX AS mx ON zb.ID=mx.ID) t WHERE t.num=1) ZJJ ON M.YPBM = ZJJ.YPBM LEFT JOIN (SELECT MIN(DJ) AS ZDDJ,AVG(DJ) AS PJDJ,
    YPBM FROM XSD_MX GROUP BY YPBM) QTDJ ON M.YPBM = QTDJ.YPBM LEFT JOIN (SELECT SUM(SL) AS SL,YPBM FROM XSD_MX SM LEFT JOIN XSD_ZB SZ ON 
    SM.ID = SZ.ID  WHERE SZ.RQ>='${agoTime}' AND SZ.RQ<= '${nowTime}' GROUP BY YPBM) XL ON M.YPBM = XL.YPBM WHERE M.ID = '${body.orderId}'`;
    let result = await sql.query(startSql);
    ctx.body = {
        data: result.recordset
    };
}

// 生成id
const createId = async (userId, tableName) => {
    let lastOrderSql = `SELECT TOP 1 ID FROM ${tableName} WHERE ID LIKE '${baseFn.getNowTimeId().date}${userId}12%' ORDER BY ID DESC`;
    let oldOrder = await sql.query(lastOrderSql);
    let newSerialNum = 1;
    if (oldOrder.recordset.length !== 0) {
        let oldOrderId = oldOrder.recordset[0].ID;
        let oldSerialNum = oldOrderId.slice(oldOrderId.length - 4);
        newSerialNum = Number(oldSerialNum) + 1;
    }
    newSerialNum = newSerialNum.toString();
    newSerialNum = '0000'.slice(0, 4 - newSerialNum.length) + newSerialNum;
    return baseFn.getNowTimeId().date + userId + '12' + newSerialNum;
}

// 保存门店采购审核的信息
const keepVeriryStoreSalesPurchaseInfo = async (ctx, next) => {

    try {
        let getBody = ctx.request.body;
        let shopList = getBody.shopList;
        let orderList = getBody.orderList;

        // 生成销订单id
        let XddNewId = await createId(getBody.userId, 'XDD_ZB');

        // 删除订货单明细
        let deleteDhd_MxSql = `DELETE FROM DHD_MX WHERE ID = '${orderList.ID}'`;

        /**
       *    写入销订单明细表
       */

        let insertXdd_MxSql = `INSERT INTO XDD_MX (ID,XH,NB,YPBM,YPTM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,YXQX,JLDW,MJSL,SL,JS,SS,YJ,YJ_HS,ZK,DJ,DJ_HS,DJ_LSJ,
            DJ_ZDJ,JE,HJ,JFGZ,KYSL,YFSL,WFSL,ZP,DZ,JF,CB)`;
        /**
         *    写入订货单明细表
         */

        let insertDhd_MxSql = `INSERT INTO DHD_MX (ID,XH,NB,YPBM,YPTM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,YXQX,JLDW,MJSL,SL,JS,SS,YJ,YJ_HS,DJ,DJ_HS,
            JE,HJ,KCSL,YDSL,WDSL,KYSL,PFJ,LSJ,HYJ,CB)`;
        let zje = 0, zss = 0, zjs = 0, zsl = 0, zhj = 0, zcb = 0, zml = 0;
        shopList.forEach(async (item, ind) => {

            let dj = Number(item.DJ ? item.DJ : 0);
            let sl = Number(item.DHSL ? item.DHSL : 0);

            let mjsl = Number(item.MJSL ? item.MJSL : 0);
            let js = mjsl === 0 ? 0 : Math.floor(DHSL / mjsl);
            let ss = sl - (mjsl * js);
            let je = (dj * sl).toFixed(2);

            zsl += sl;
            zje += Number(je);
            zss += Number(ss);
            zjs += Number(js);

            let xhSerialNum = '00000'.slice(0, 5 - (ind + 1).toString().length) + (ind + 1);

            // 写入订货单明细
            insertDhd_MxSql += `SELECT '${orderList.ID}',${baseFn.getNowTimeId().msDate + xhSerialNum},${ind + 1},'${item.BM}','${item.TM}','${item.MC}','${item.GG}'
                ,'${item.JXMC}','${item.SCCJ}','${item.CDMC}','${item.PZWH}',${item.YXQX},'${item.JLDW}',${item.MJSL},${item.DHSL},${js},${ss},
                ${item.DJ},${item.DJ},${item.DJ},${item.DJ},${je},${je},${item.KCSL},${item.DHSL},0,${item.KYSL},${item.PFJ},${item.LSJ},${item.HYJ},'A' `;

            // 写入销订单明细
            insertXdd_MxSql += `SELECT '${XddNewId}',${baseFn.getNowTimeId().msDate + xhSerialNum},${ind + 1},'${item.BM}','${item.TM}','${item.MC}','${item.GG}'
                ,'${item.JXMC}','${item.SCCJ}','${item.CDMC}','${item.PZWH}',${item.YXQX},'${item.JLDW}',${item.MJSL},${item.DHSL},${js},${ss},
                ${item.DJ},${item.DJ},100,${item.DJ},${item.DJ},${item.LSJ},${item.ZDJ},${je},${je},'${item.JFGZ}',${item.KYSL},0,${item.DHSL},'F','T','T','A' `;

            if (ind !== (shopList.length - 1)) {
                insertDhd_MxSql += ` UNION `;
                insertXdd_MxSql += ` UNION `;
            }

        });

        /**
        *   更新订货单结算表
        */
        let updateDhd_JsSql = `UPDATE DHD_JS SET JSJE = ${zje} WHERE ID = '${orderList.ID}'`;

        // 获取结算方式信息
        let jsfsResult = await sql.query(`SELECT BM,MC,KM1BM,KMBM,KMQC FROM DHD_JS WHERE ID = '${orderList.ID}'`);
        let jsfsInfo = jsfsResult.recordset[0];
        /**
       *   插入销订单结算表
       */
        let insertXdd_JsSql = `INSERT INTO XDD_JS(ID,XH,BM,MC,KM1BM,KMBM,KMQC,JSJE) VALUES ('${XddNewId}',1,'${jsfsInfo.BM}','${jsfsInfo.MC}',
       '${jsfsInfo.KM1BM}','${jsfsInfo.KMBM}','${jsfsInfo.KMQC}',${zje})`;

        /**
        *   更新订货单总表
        */
        let updateDhd_ZbSql = `UPDATE DHD_ZB SET ZSL= ${zsl},ZJS= ${zjs},ZSS= ${zss},ZJE= ${zje},ZHJ= ${zje},YJS= ${zje},WJS= ${zje},SHR='${getBody.SHR}',SH='T' WHERE ID = '${orderList.ID}'`;


        /**
        *   插入销订单总表
        */
        let insertXdd_ZbSql = '';
        if (orderList.BMBM !== '01' && orderList.BMBM !== '07') {
            //通过部门编码匹配供应商写入xdd_zb 
            let matchList = { '02': '2010001', '03': '2010002', '04': '2010003', '05': '2010004', '06': '2010005', '08': '2010006' };
            let supplier = await sql.query(`SELECT MC FROM BM_WLDW WHERE BM = '${matchList[orderList.BMBM]}'`);
            insertXdd_ZbSql = `INSERT INTO XDD_ZB(ID,RQ,DWBM,DWMC,BMBM,BMMC,JBRBM,JBRMC,JSFSBM,JSFSMC,ZDR,SHR,ZDZK,ZSL,
            ZJS,ZSS,ZJE,ZSE,ZHJ,YJS,EJS,WJS,LX,SH) VALUES ('${XddNewId}','${baseFn.getNowTimeId().fullDate}','${matchList[orderList.BMBM]}',
            '${supplier.recordset[0].MC}','07','第三方平台店','${getBody.userId}','${getBody.SHR}','${orderList.JSFSBM}',
            '${orderList.JSFSMC}','${getBody.SHR}','${getBody.SHR}',100,${zsl},${zjs},${zss},${zje},0,${zje},${zje},0,${zje},'批发','T')`;
        }

        
        // 几个表同时插入，产生错误就回滚
        let lastSql = `SET XACT_ABORT ON Begin Tran ${deleteDhd_MxSql} ${insertDhd_MxSql} ${updateDhd_ZbSql} ${updateDhd_JsSql} `;
        //如果不是01或07部门的就写入销售订单表
        if (orderList.BMBM !== '01' && orderList.BMBM !== '07') {
            lastSql += `${insertXdd_MxSql}  ${insertXdd_JsSql}  ${insertXdd_ZbSql} `;
        }
        lastSql += ` Commit Tran`;
        let lastResult = await sql.query(lastSql);
        if (lastResult.rowsAffected[0] > 0) {
            ctx.body = { success: true }
        } else {
            ctx.body = { success: false }
        }
    } catch (err) {
        ctx.body = { success: false, err }
    }
}


module.exports = {
    'POST /getVerifyStorePurchaseOrder': getVerifyStorePurchaseOrder,
    'POST /getVerifyStorePurchaseShop': getVerifyStorePurchaseShop,
    'POST /keepVeriryStoreSalesPurchaseInfo': keepVeriryStoreSalesPurchaseInfo
}
